package com.affablebeans.model.dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.affablebeans.model.entity.Customer;
import com.affablebeans.model.entity.Order;
import com.affablebeans.model.entity.Product;

/**
 *
 * @author Bora SAYINER
 * @since Jan 26, 2015 5:41:39 PM
 * @version 1.0
 */
public class OrderDAL extends AbstractDAL<Order> {

	private static final String INSERT_SQL = "INSERT INTO customer_order (amount,date_created,confirmation_number,customer_id) VALUES (?,?,?,?)";
	private static final String UPDATE_SQL = "UPDATE customer_order SET amount=?,date_created=?,confirmation_number=?,customer_id=? WHERE id=?";
	private static final String DELETE_SQL = "DELETE FROM customer_order WHERE id=?";
	private static final String FIND_BY_ID_SQL = "SELECT co.id,co.amount,date_created,co.confirmation_number,c.id AS customer_id,c.name,c.email,c.phone,c.address,c.city_region,c.cc_number FROM customer_order AS co INNER JOIN customer AS c ON c.id=co.customer_id WHERE id=?";
	private static final String GET_ALL_SQL = "SELECT co.id,co.amount,date_created,co.confirmation_number,c.id AS customer_id,c.name,c.email,c.phone,c.address,c.city_region,c.cc_number FROM customer_order AS co INNER JOIN customer AS c ON c.id=co.customer_id";
	private static final String FIND_BY_CUSTOMER_SQL = "SELECT co.id,co.amount,date_created,co.confirmation_number,c.id AS customer_id,c.name,c.email,c.phone,c.address,c.city_region,c.cc_number FROM customer_order AS co INNER JOIN customer AS c ON c.id=co.customer_id WHERE c.id=?";

	public OrderDAL(DataSource dataSource) {
		super(dataSource);
	}

	@Override
	public boolean insert(Order entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS)) {
				statement.setDouble(1, entity.getAmount());
				statement.setDate(2, convertToDate(entity.getCreatedDate()));
				statement.setInt(3, entity.getConfirmationNumber());
				statement.setInt(4, entity.getCustomer().getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
				if (result) {
					ResultSet generatedKeys = statement.getGeneratedKeys();
					if (generatedKeys.next()) {
						entity.setId(generatedKeys.getInt(1));
					}
				}
			}
		}
		return result;
	}

	@Override
	public boolean update(Order entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(UPDATE_SQL)) {
				statement.setDouble(1, entity.getAmount());
				statement.setDate(2, convertToDate(entity.getCreatedDate()));
				statement.setInt(3, entity.getConfirmationNumber());
				statement.setInt(4, entity.getCustomer().getId());
				statement.setInt(5, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public boolean delete(Order entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(DELETE_SQL)) {
				statement.setInt(1, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public Order findById(int id) throws SQLException {
		Order order = null;
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(FIND_BY_ID_SQL)) {
				statement.setInt(1, id);
				ResultSet resultSet = statement.executeQuery();
				if (resultSet.next()) {
					order = createEntity(resultSet);
				}
			}
		}
		return order;
	}

	@Override
	public List<Order> getAll() throws SQLException {
		List<Order> orders = new ArrayList<Order>();
		try (Connection connection = createConnection()) {
			try (Statement statement = connection.createStatement()) {
				ResultSet resultSet = statement.executeQuery(GET_ALL_SQL);
				while (resultSet.next()) {
					orders.add(createEntity(resultSet));
				}
			}
		}
		return orders;
	}
	
	public List<Product> findByCustomer(int id) throws SQLException {
		List<Product> products = new ArrayList<Product>();
		try (Connection connection = createConnection()) {
			try (PreparedStatement statement = connection.prepareStatement(FIND_BY_CATEGORY_SQL)) {
				statement.setInt(1, id);
				ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					products.add(createEntity(resultSet));
				}
			}
		}
		return products;
	}

	@Override
	Order createEntity(ResultSet resultSet) throws SQLException {
		Order order = new Order();
		order.setId(resultSet.getInt("id"));
		order.setAmount(resultSet.getDouble("amount"));
		order.setCreatedDate(resultSet.getDate("date_created"));
		order.setConfirmationNumber(resultSet.getInt("confirmation_number"));
		{
			Customer customer = new Customer();
			customer.setId(resultSet.getInt("customer_id"));
			customer.setName(resultSet.getString("name"));
			customer.setEmail(resultSet.getString("email"));
			customer.setPhone(resultSet.getString("phone"));
			customer.setAddress(resultSet.getString("address"));
			customer.setCityRegion(resultSet.getString("city_region"));
			customer.setCreditCartNumber(resultSet.getString("cc_number"));
			order.setCustomer(customer);
		}
		return order;
	}

}
